One type of electronic information storage system is a relational database. Data records in a relational database management system are maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records that comprise the rows. One or more indexes may be associated with each table. An index is an ordered set of pointers to data records in the table based on the data in one or more columns of the table. An index is comprised of rows or index entries that include an index key and a pointer to a database record in the table having the key column values of the index entry key. An index key comprises key columns that provide an ordering to records in a table. The index key columns comprise the columns of the table, and may include any of the values that are possible for that particular column. Columns that are used frequently to access a table may be used as key columns.
The information contained in a relational database is referred to as being “flat.” In other words, the data in such a database is not hierarchically organized. Any relationships between the data is defined by pointers and the like. In contrast, the object-oriented programs that often interact with relational databases are not adapted to interpret and use flat data. Instead, object-oriented programs hierarchically organize data objects into classes. An example of hierarchically organized data is a data object “tree,” where the relationships between the data objects are represented by the object's position in the tree. Thus, the flat data contained in the database needs to be converted into a form that can be accessed programatically if an object-oriented program is to properly interact with the data contained in the relational database.
From the standpoint of an object-oriented software developer, it is desirable to know the schema of a particular relational database for purposes of rendering the database's flat data useable to an object-oriented program. The schema is a definition of all or part of a database. For example, the schema defines the structure, the type of contents that can be in each data object within the structure, and relationships between the objects. Importantly, for development purposes, the schema provides all the information necessary for a developer to create software code that will be able to interact with the database. The actual data content (e.g., the instances of a particular class) stored in the database is irrelevant for this purpose and would slow the development process if the content had to be handled and transmitted. The schema is described by database server metadata that is stored in a relational data store. The relational data store consists of tables that contain metadata about database objects.
MICROSOFT SQL SERVER, for example, is a comprehensive database management platform that provides extensive database management and development tools, a powerful extraction, transformation, and loading (ETL) tool, business intelligence and analysis services, and other capabilities. To enable deployment of a database solution from a development server to a production server, it is necessary to generate a SQL creation script for existing database objects. The creation script, if applied, recreates the schema of the original database, or the portions of the original database, that was scripted. Because of this feature, the creation script helps to transfer the database metadata between servers. The generation of such a creation script is commonly referred to as “scripting the schema.” The “deployment” of a solution typically means that the database schema of the production server is identical to that of the development server, because database developers use the development server to test for more efficient schemas. In some applications, deployment means only applying small changes to the schema, while in other applications, as noted above, the entire schema is copied.
An intermediate step in the process of generating a creation script is to create an object tree model that is populated with the database server metadata from the relational data store. The populated tree is used to represent the manageable entries contained in a database server, along with the entities' associated metadata. The tree is a necessary intermediate step in the creation script generation process because, as noted above, object-oriented programs are not adapted to interpret the metadata contained in the relational data store without the additional hierarchy information provided by the tree structure. Thus, the populated tree permits programmatic access to the metadata to generate the creation script because the metadata has been transformed into a format that is accessible to an object-oriented program.
Conventional processes for creating a tree object model are slow and processor-intensive because they issue a large number of queries to obtain the necessary schema information. In the SQL SERVER world, the Distributed Management Objects (DMO) management library implements one such conventional scripting process. For example, as the methodology employed by the DMO “walks” through the tree, a query is issued every time a lower level of an object in the tree needs to be populated. Programmatically, this is done by querying the database whenever a node (object) in the tree is accessed and not fully initialized. Thus, this conventional process issues a number of queries that is proportional to the number of objects stored in the database, which causes the process to become slower as the number of objects becomes larger. As a result, such a conventional scripting method is not easily scalable because the method becomes less and less efficient as the amount of data to be scripted increases.
What is needed, therefore, is a scalable method of scripting a database schema that issues a number of queries that is not proportional to the number of objects in a database, thereby increasing the speed and reducing the complexity of the tree population and, therefore, scripting process. More particularly, what is needed is a scripting method that leverages prior knowledge of the tree structure to more efficiently query the data store by only issuing one query for every type of object, regardless of the number of objects of a particular type.